﻿<cfscript>
/** 
* @hint "应届毕业生相关监听器"
*/ 

component 
	extends="MachII.framework.Listener" 
	displayname="acadmicManager.listeners.grad.newGradListener" 
	output=false 
	depends="sessionAdvice, datetimeAdvice, creditFactory, markAdvice, sequenceSchemaCourseReplace, sequenceStudentCourseReplace, graduateFactory, degreeFactory" 
	{
		
	/**
	* @hint "Configures this listener as part of the Mach-II framework"
	*/
	public void function configure() output=false {} 
	
	
	/**
	* @hint "应届毕业生 - 自动修正课程性质和学分统计"
	* 
	* @event "MachII Event Object"
	*/
	public void function flushByStudentID( required MachII.framework.Event event ) output=false {
		
		var creditFactory = getcreditFactory();
		
		var studentId = event.getArg("STU");
		
		if ( len(studentId) ) {
			
			/* 刷新指定学生的学分统计数据 */
			creditFactory.creditRefresh( studentId ); 
			
		}

	}
	
	/**
	* @hint "应届毕业生 - 刷新毕业审核各项条件状态"
	* 
	* @event "MachII Event Object"
	*/
	public void function flushGradStatByStudentID( required MachII.framework.Event event ) output=false {
		
		var graduateFactory = getgraduateFactory();
		
		var studentId = event.getArg("STU");
		
		if ( len(studentId) ) {
			
			sql = "SELECT gdc, gdc_params FROM t_grad_details WHERE stu_id = :studentId";
			
			queryObj = new Query( datasource=application.dnsMaster );
			queryObj.addParam( name="studentId", value=studentId, cfsqltype="cf_sql_varchar" );
			
			var rs_grad = queryObj.execute( sql=sql ).getResult();
			
			if( NOT arrayFind(rs_grad["gdc"],"CPR") ){
		
				sql = "DELETE FROM t_mark_need t
							WHERE
								t.stu_id = :studentId 
								AND
								t.gdc = 'CPR' ";
				
				queryObj = new Query( datasource=application.dnsMaster );
				
				queryObj.addParam( name="studentId", value=studentId, cfsqltype="cf_sql_varchar" );
				
				queryObj.execute( sql=sql );
			}
			
			if( NOT arrayFind(rs_grad["gdc"],"CBR") ){
				
				sql = "DELETE FROM t_mark_need t
							WHERE
								t.stu_id = :studentId 
								AND
								t.gdc = 'CBR' ";
				
				queryObj = new Query( datasource=application.dnsMaster );
				
				queryObj.addParam( name="studentId", value=studentId, cfsqltype="cf_sql_varchar" );
				
				queryObj.execute( sql=sql );
			}
			
			for ( var a=1; a LTE rs_grad.recordCount; a++ ) {
				
				var templateId = rs_grad["gdc"][a];
				var templateParams = rs_grad["gdc_params"][a];
				
				graduateFactory.audit( templateId, studentId, templateParams );
				
			}
			
			/* 更新机器审核结论 */
			graduateFactory.auditPass( studentId );
			
		}

	}
	
	/**
	* @hint "应届毕业生 - 刷新学位审核各项条件状态"
	* 
	* @event "MachII Event Object"
	*/
	public void function flushDegreeStatByStudentID( required MachII.framework.Event event ) output=false {

		var degreeFactory = getdegreeFactory();
		
		var studentId = event.getArg("STU");
		
		if ( len(studentId) ) {
			
			sql = "SELECT gdc, gdc_params FROM t_degree_details WHERE stu_id = :studentId";
			
			queryObj = new Query( datasource=application.dnsMaster );
			queryObj.addParam( name="studentId", value=studentId, cfsqltype="cf_sql_varchar" );
			
			var rs_degree = queryObj.execute( sql=sql ).getResult();
			
			if( NOT arrayFind(rs_degree["gdc"],"CPD")){
					
					sql = "DELETE FROM
								t_mark_need t
							WHERE
								t.stu_id = :studentId 
								AND
								t.gdc = 'CPD' ";
					
					queryObj = new Query( datasource=application.dnsMaster );
					
					queryObj.addParam( name="studentId", value=studentId, cfsqltype="cf_sql_varchar" );
					
					queryObj.execute( sql=sql );
			}
			
			if( NOT arrayFind(rs_degree["gdc"],"CBD")){
					
					sql = "DELETE FROM
								t_mark_need t
							WHERE
								t.stu_id = :studentId 
								AND
								t.gdc = 'CBD' ";
					
					queryObj = new Query( datasource=application.dnsMaster );
					
					queryObj.addParam( name="studentId", value=studentId, cfsqltype="cf_sql_varchar" );
					
					queryObj.execute( sql=sql );
			}
			
			for ( var a=1; a LTE rs_degree.recordCount; a++ ) {
				
				var templateId = rs_degree["gdc"][a];
				var templateParams = rs_degree["gdc_params"][a];
				
				degreeFactory.audit( templateId, studentId, templateParams );
				
			}
			
			/*更新学位课审核结论*/
			degreeFactory.auditPass( studentId );
			
		}

	}
	
	
	/**
	* @hint "自动标注指定学生的课程替代关系, 用于自动处理相同 课程名称/课程号 的成绩, 此种通常为正常的重修成绩替换"
	* 
	* @event "MachII Event Object"
	*/
	public void function autoSignMarkReplace( required MachII.framework.Event event ) output=false {
		
		var sql = "";
		var queryObj = "";
		
		var markAdvice = getmarkAdvice();

		var studentId = event.getArg("STU");
		
		if ( len(studentId) ) {
			
			/* Every SQL operation is TRANSACATION*/
			transaction {
				
				/* 当前日期时间字符串 */
				var currentDateTime = getdatetimeAdvice().getCurrentDatetimeString();
			
				/* 当前审阅人 */
				var teacherId = getsessionAdvice().getAutherUserID();
				
				/* 读取未通过课程 且这些课程没有标注替代 */
				sql = "SELECT 
							b.cid, b.course_name, 
							a.scm_id 
							FROM t_student_mark a 
								INNER JOIN t_course b ON b.cid = a.cid 
								WHERE 
						 			a.stu_id = :studentId 
									AND 
									( 
										a.mark_result < 60 AND a.mark_resit < 60 
									) 
									AND NOT EXISTS (
										SELECT 1 FROM t_mark_replace c 
										WHERE 
											a.scm_id = c.scm_id
									) ";

				queryObj = new Query( datasource=application.dnsMaster );
				queryObj.addParam( name="studentId", value=studentId, cfsqltype="cf_sql_varchar" );
			
				var rs_failure = queryObj.execute( sql=sql ).getResult();
				
				
				/* 读取已通过成绩 */
				sql = "SELECT 
							b.cid, b.course_name, b.course_code, 
							a.retaking, a.mark_mode, 
							a.mark_result, a.mark_note, 
							a.mark_resit, a.mark_renote, a.mark_resit_limit, 
							c.academic_year, c.academic_term
							FROM t_student_mark a 
								INNER JOIN t_course b ON b.cid = a.cid 
								INNER JOIN t_term c ON c.term_id = a.term_id 
								WHERE 
						 			a.stu_id = :studentId 
									AND 
									( 
										a.mark_result >= 60 OR a.mark_resit >= 60 
									) ";

				queryObj = new Query( datasource=application.dnsMaster );
				queryObj.addParam( name="studentId", value=studentId, cfsqltype="cf_sql_varchar" );
			
				var rs_passed = queryObj.execute( sql=sql ).getResult();
				
				
				/* 历遍所有的不及格成绩 查找可替换课程 */
				for ( var a=1; a LTE rs_failure.recordCount; a++ ) {
						
					var courseName = rs_failure["course_name"][a];
					var courseId = rs_failure["cid"][a];
					var markId = rs_failure["scm_id"][a];
					
					/* 
						QoQ Search 
						从已通过成绩中, 查找成绩的最高值
					*/
					sql = "SELECT * FROM query
								WHERE 
									course_name = :courseName OR cid = :courseId 
								ORDER BY 
									mark_result DESC, mark_resit DESC";

					queryObj = new Query( dbtype="query" );
					queryObj.setAttributes( query=rs_passed );
					queryObj.addParam( name="courseName", value=courseName, cfsqltype="cf_sql_varchar" );
					queryObj.addParam( name="courseId", value=courseId, cfsqltype="cf_sql_varchar" );

					var rs_finder = queryObj.execute( sql=sql ).getResult();
					
					if ( rs_finder.recordCount ) {
						
						/* 有可替换成绩 */
						var courseNameReplaced = rs_finder.course_name & " (" & rs_finder.course_code & ")";
						
						var markReplaced = markAdvice.getMarkResult( rs_finder.mark_mode, rs_finder.mark_result, rs_finder.mark_note, rs_finder.mark_resit, rs_finder.mark_renote, rs_finder.mark_resit_limit );

						var description = "使用 " & rs_finder.academic_year & "-" & rs_finder.academic_term & "学期《" & rs_finder.course_name & "》, 考核成绩 " & markReplaced & " 替换此项不及格课程";

						sql = "INSERT INTO t_mark_replace 
								( scm_id, rep_course, rep_mark, rep_desc, tch_id, date_created ) 
								VALUES 
								( :markId, :courseNameReplaced, :markReplaced, :description, :teacherId, :currentDateTime )";

						queryObj = new Query( datasource=application.dnsMaster );
						queryObj.addParam( name="markId", value=markId, cfsqltype="cf_sql_varchar" );
						queryObj.addParam( name="courseNameReplaced", value=courseNameReplaced, cfsqltype="cf_sql_varchar" );
						queryObj.addParam( name="markReplaced", value=markReplaced, cfsqltype="cf_sql_varchar" );
						queryObj.addParam( name="description", value=description, cfsqltype="cf_sql_varchar" );
						queryObj.addParam( name="teacherId", value=teacherId, cfsqltype="cf_sql_varchar" );
						queryObj.addParam( name="currentDateTime", value=currentDateTime, cfsqltype="cf_sql_char" );
						
						queryObj.execute( sql=sql );

					}
					
					
				}
				
				
			}

		}
		
	}
	

	/**
	* @hint "应届毕业生 - 初始化指定专业、年级的毕业生数据"
	* 
	* @event "MachII Event Object"
	*/
	public void function initGradBySubjectGrade( required MachII.framework.Event event ) output=false {
		
		var sql = "";
		var queryObj = "";
		
		var subjectId = event.getArg("SBJ");
		var grade = event.getArg("GRD");
		
		if ( len(subjectId) and len(grade) ) {

			/* Every SQL operation is TRANSACATION*/
			transaction {
			
				/* 当前日期时间字符串 */
				var currentDateTime = getdatetimeAdvice().getCurrentDatetimeString();
			
				/* 当前审阅人 */
				var teacherId = getsessionAdvice().getAutherUserID();
				
				/* 根据指定专业、年级生成毕业生学分统计默认数据 */
				sql = "INSERT INTO t_student_profile 
						(
							stu_id, 
							credit, credit_req, credit_opt, credit_phy, credit_com, credit_failure,
							credit_req_pt, credit_req_avgpt, 
							credit_pt, credit_avgpt,
							subject_rank, subject_idx, 
							subject_rank_all, subject_idx_all, 
							credit_grad, credit_grad_pt, credit_grad_avgpt
						) 
						SELECT 
							a.stu_id, 
							0, 0, 0, 0, 0, 0, 
							0, 0, 
							0, 0, 
							0, 0, 0, 0, 
							0, 0, 0
							FROM t_student a 
							INNER JOIN t_class b ON b.cls_id = a.cls_id 
							INNER JOIN t_subject c ON c.sbj_id = b.sbj_id 
							INNER JOIN t_student_status d ON d.stu_id = a.stu_id 
							WHERE 
								b.grade = :grade 
								AND 
								c.sbj_id = :subjectId 
								AND 
								SUBSTR( d.student_prop, 1, 1 ) = '1' 
								AND 
								NOT EXISTS (
									SELECT 1 FROM t_student_profile e 
									WHERE a.stu_id = e.stu_id 
								) "; 
			
				queryObj = new Query( datasource=application.dnsMaster );
			
				queryObj.addParam( name="currentDateTime", value=currentDateTime, cfsqltype="cf_sql_char" );
				queryObj.addParam( name="teacherId", value=teacherId, cfsqltype="cf_sql_varchar" );
				queryObj.addParam( name="grade", value=grade, cfsqltype="cf_sql_char" );
				queryObj.addParam( name="subjectId", value=subjectId, cfsqltype="cf_sql_varchar" );

				queryObj.execute( sql=sql );


				/* 根据指定专业、年级生成毕业生预审核名单 */
				sql = "INSERT INTO t_grad_review 
						(
						stu_id, is_passed, comp_passed, review_passed, tch_id, date_created, date_review, gdc_passed, gdc_failure 
						) 
						SELECT 
							a.stu_id, '-', '-', '-', :teacherId, :currentDateTime, :currentDateTime, 0, 0 
							FROM t_student a 
							INNER JOIN t_class b ON b.cls_id = a.cls_id 
							INNER JOIN t_subject c ON c.sbj_id = b.sbj_id 
							INNER JOIN t_student_status d ON d.stu_id = a.stu_id 
							WHERE 
								b.grade = :grade 
								AND 
								c.sbj_id = :subjectId 
								AND 
								SUBSTR( d.student_prop, 1, 1 ) = '1' 
								AND 
								NOT EXISTS (
									SELECT 1 FROM t_grad_review e 
									WHERE a.stu_id = e.stu_id 
								) "; 
			
				queryObj = new Query( datasource=application.dnsMaster );
			
				queryObj.addParam( name="currentDateTime", value=currentDateTime, cfsqltype="cf_sql_char" );
				queryObj.addParam( name="teacherId", value=teacherId, cfsqltype="cf_sql_varchar" );
				queryObj.addParam( name="grade", value=grade, cfsqltype="cf_sql_char" );
				queryObj.addParam( name="subjectId", value=subjectId, cfsqltype="cf_sql_varchar" );
			
				
				queryObj.execute( sql=sql );

			}
			
		}
		
	}

	/**
	* @hint "学位授予 - 初始化指定专业、年级的授予学位已毕业学生数据"
	* 
	* @event "MachII Event Object"
	*/
	public void function initGradDegreeBySubjectGrade ( required MachII.framework.Event event ) output=false {
		
		var sql = "";
		var queryObj = "";
		
		var subjectId = event.getArg("SBJ");
		var grade = event.getArg("GRD");
		
		if ( len(subjectId) and len(grade) ) {

			/* Every SQL operation is TRANSACATION*/
			transaction {
			
				/* 当前日期时间字符串 */
				var currentDateTime = getdatetimeAdvice().getCurrentDatetimeString();
			
				/* 当前审阅人 */
				var teacherId = getsessionAdvice().getAutherUserID();
				
		
				/* 根据指定专业、年级生成毕业生预审核名单 */
				sql = "INSERT INTO t_degree_review 
						(
						stu_id, is_passed, comp_passed, review_passed, tch_id, date_created, date_review 
						) 
						SELECT 
							a.stu_id, '-', '-', '-', :teacherId, :currentDateTime, :currentDateTime 
							FROM t_student a 
							INNER JOIN t_class b ON b.cls_id = a.cls_id 
							INNER JOIN t_subject c ON c.sbj_id = b.sbj_id 
							INNER JOIN t_student_status d ON d.stu_id = a.stu_id 
							INNER JOIN t_grad_review f ON f.stu_id = a.stu_id 
							WHERE 
								b.grade = :grade 
								AND 
								c.sbj_id = :subjectId 
								AND 
								SUBSTR( d.student_prop, 1, 1 ) = '1' 
								AND 
								f.is_passed = '1' 
								AND 
								NOT EXISTS (
									SELECT 1 FROM t_degree_review e 
									WHERE a.stu_id = e.stu_id 
								) "; 
			
				queryObj = new Query( datasource=application.dnsMaster );
			
				queryObj.addParam( name="currentDateTime", value=currentDateTime, cfsqltype="cf_sql_char" );
				queryObj.addParam( name="teacherId", value=teacherId, cfsqltype="cf_sql_varchar" );
				queryObj.addParam( name="grade", value=grade, cfsqltype="cf_sql_char" );
				queryObj.addParam( name="subjectId", value=subjectId, cfsqltype="cf_sql_varchar" );
			
				
				queryObj.execute( sql=sql );

			}
			
		}
		
	}

	/**
	* @hint "学生成绩替代"
	* 
	* @event "MachII Event Object"
	*/
	public void function markReplace( required MachII.framework.Event event ) output=false {
		
		var markAdvice = getmarkAdvice();
		var sessionAdvice = getsessionAdvice();
		var sequenceSchemaCourseReplace = getsequenceSchemaCourseReplace();
		
		var teacherId = sessionAdvice.getAutherUserID();
		var dateCreated = dateFormat(now(),"YYYYMMDD");
		
		var sql = "";
		var queryObj = "";
		
		var subjectId = event.getArg("SBJ");
		var grade = event.getArg("GRD");
		var studentId = event.getArg("STU");
		
		var oldMarkId = event.getArg("oldMarkId");
		var newMarkId = event.getArg("newMarkId");
		
		var toSubject = event.getArg("toSubject"); /* 该替代关系是否适用于整个专业 */
		var toCourse = event.getArg("toCourse"); /* 如果该门课程有多个不及格成绩, 对所有成绩都使用这个规则进行标注 */


		transaction {

			/*查询旧成绩信息*/
			sql = "	SELECT c.cid
						FROM t_student_mark m 
							INNER JOIN t_course c ON c.cid = m.cid 
						WHERE 
							m.scm_id = :markId ";

			queryObj = new Query( datasource=application.dnsMaster );
			queryObj.addParam( name="markId", value=oldMarkId, cfsqltype="cf_sql_varchar" );

			var rs_old_mark = queryObj.execute(sql=sql).getResult();
			
			/*查询新成绩信息*/
			sql = "	SELECT 
						c.course_name, c.cid, c.course_code, 
						m.mark_mode, m.mark_result, m.mark_note, m.mark_resit, m.mark_renote, m.mark_resit_limit, 
						t.academic_year,t.academic_term 
					FROM
						t_student_mark m INNER JOIN t_course c ON c.cid = m.cid 
						INNER JOIN t_term t ON t.term_id = m.term_id 
					WHERE
						m.scm_id = :markId ";
			
			queryObj = new Query( datasource=application.dnsMaster );
			queryObj.addParam( name="markId", value=newMarkId, cfsqltype="cf_sql_varchar" );
			
			var rs_new_mark = queryObj.execute(sql=sql).getResult();
			
			/*准备参数*/
			var newCourseName = rs_new_mark.course_name & " (" & rs_new_mark.course_code & ")";
			var newMark = markAdvice.getMarkResult(
						rs_new_mark.mark_mode,
						rs_new_mark.mark_result,
						rs_new_mark.mark_note,
						rs_new_mark.mark_resit,
						rs_new_mark.mark_renote,
						rs_new_mark.mark_resit_limit
				);

			var description = "使用 " & rs_new_mark.academic_year & "-" & rs_new_mark.academic_term & "学期《" & rs_new_mark.course_name & "》, 考核成绩 " & newMark & " 替换此项不及格课程";
			
			/* 新旧课程代码 */
			var courseIdOld = rs_old_mark.cid;
			var courseIdNew = rs_new_mark.cid;
			
			
			/*检查成绩替代是否已经存在*/
			sql = "SELECT 1 FROM t_mark_replace WHERE scm_id = :markId ";
			
			queryObj = new Query( datasource=application.dnsMaster );
			queryObj.addParam( name="markId", value=oldMarkId, cfsqltype="cf_sql_varchar" );
			
			var rs_check = queryObj.execute(sql=sql).getResult();
			
			if( rs_check.recordCount eq 0 ){
			
				sql = "	INSERT INTO t_mark_replace 
							(
								scm_id,
								rep_course,
								rep_mark,
								rep_desc,
								tch_id,
								date_created
							) 
							VALUES( 
								:markId, 
								:courseName, 
								:mark, 
								:description, 
								:teacherId, 
								:dateCreated 
							)";
				
				queryObj = new Query( datasource=application.dnsMaster );
				
				queryObj.addParam( name="markId", value=oldMarkId, cfsqltype="cf_sql_varchar" );
				queryObj.addParam( name="courseName", value=newCourseName, cfsqltype="cf_sql_varchar" );
				queryObj.addParam( name="mark", value=newMark, cfsqltype="cf_sql_varchar" );
				queryObj.addParam( name="description", value=description, cfsqltype="cf_sql_varchar" );
				queryObj.addParam( name="teacherId", value=teacherId, cfsqltype="cf_sql_varchar" );
				queryObj.addParam( name="dateCreated", value=dateCreated, cfsqltype="cf_sql_char" );
				
				queryObj.execute( sql=sql );
				
			}
			
			/* 如果该课程有多门重复成绩, 进行重复替代 */
			if( len(toCourse) and toCourse eq "1" ) {
				
				/* 查找对应的不及格课程 */
				sql = "INSERT INTO t_mark_replace a 
						( 
							scm_id, 
							rep_course, 
							rep_mark, 
							rep_desc, 
							tch_id, 
							date_created 
						) 
						SELECT 
							scm_id, 
							:courseName, 
							:mark, 
							:description, 
							:teacherId, 
							:dateCreated 
						FROM t_student_mark b 
						WHERE 
							b.stu_id = :studentId 
							AND 
							b.cid = :courseId 
							AND 
								( 
									b.mark_result < 60 AND b.mark_resit < 60 
								)
							AND 
							NOT EXISTS ( 
								SELECT 1 FROM t_mark_replace a 
								WHERE 
									a.scm_id = b.scm_id 
							) ";
				
				
				queryObj = new Query( datasource=application.dnsMaster );
				
				queryObj.addParam( name="courseName", value=newCourseName, cfsqltype="cf_sql_varchar" );
				queryObj.addParam( name="mark", value=newMark, cfsqltype="cf_sql_varchar" );
				queryObj.addParam( name="description", value=description, cfsqltype="cf_sql_varchar" );
				queryObj.addParam( name="teacherId", value=teacherId, cfsqltype="cf_sql_varchar" );
				queryObj.addParam( name="dateCreated", value=dateCreated, cfsqltype="cf_sql_char" );
				queryObj.addParam( name="courseId", value=rs_old_mark.cid, cfsqltype="cf_sql_char" );
				queryObj.addParam( name="studentId", value=studentId, cfsqltype="cf_sql_char" );
				
				queryObj.execute( sql=sql );
				
				
				/* 将成绩替代规则应用到整个专业 并且此时使用课程替换模式 */
				if( len(toSubject) and toSubject eq "1" ) {
					
					/* 
						读取专业学生名单, 并且这些学生都有 
						被当前课程不及格 与 指定课程及格成绩
					*/
					sql = "SELECT 
								a.stu_id 
								FROM t_grad_review a 
								INNER JOIN t_student b ON a.stu_id = b.stu_id 
								INNER JOIN t_class c ON c.cls_id = b.cls_id 
								WHERE 
									c.sbj_id = :subjectId 
									AND 
									c.grade = :grade 
									AND EXISTS (
										SELECT 1 FROM t_student_mark d 
										WHERE 
											d.stu_id = a.stu_id 
											AND 
											(
												d.cid = :courseIdOld AND d.mark_result < 60 AND d.mark_resit < 60 
											) 
									) 
									AND EXISTS (
										SELECT 1 FROM t_student_mark e 
										WHERE 
											e.stu_id = a.stu_id 
											AND 
											(
												e.cid = :courseIdNew AND ( e.mark_result >= 60 OR e.mark_resit >= 60 )
											) 
									) ";
					
					queryObj = new Query( datasource=application.dnsMaster );
					
					queryObj.addParam( name="courseIdOld", value=courseIdOld, cfsqltype="cf_sql_varchar" );
					queryObj.addParam( name="courseIdNew", value=courseIdNew, cfsqltype="cf_sql_varchar" );
					queryObj.addParam( name="subjectId", value=subjectId, cfsqltype="cf_sql_varchar" );
					queryObj.addParam( name="grade", value=grade, cfsqltype="cf_sql_char" );
				
					var rs_student = queryObj.execute(sql=sql).getResult();
					
					for ( var a=1; a LTE rs_student.recordCount; a++ ) {
						
						/* 历遍学生进行成绩替代标注 */
						var studentIdPatch = rs_student.stu_id; 
						
						/* 针对学生个人 查找代替课程的最高成绩 */
						sql = "SELECT 
									c.course_name, c.cid, c.course_code, 
									m.mark_mode, m.mark_result, m.mark_note, m.mark_resit, m.mark_renote, m.mark_resit_limit, 
									t.academic_year,t.academic_term 
								FROM t_student_mark m 
									INNER JOIN t_course c ON c.cid = m.cid 
									INNER JOIN t_term t ON t.term_id = m.term_id 
								WHERE 
									m.stu_id = :studentIdPatch 
									AND 
									m.cid = :courseIdNew 
								ORDER BY 
									m.mark_result DESC, m.mark_resit DESC";

						queryObj = new Query( datasource=application.dnsMaster );
						queryObj.addParam( name="studentIdPatch", value=studentIdPatch, cfsqltype="cf_sql_varchar" );
						queryObj.addParam( name="courseIdNew", value=courseIdNew, cfsqltype="cf_sql_varchar" );
			
						var rs_new_mark_patch = queryObj.execute(sql=sql).getResult();
						
						/*准备参数*/
						var newCourseNamePatch = rs_new_mark.course_name & " (" & rs_new_mark.course_code & ")";
						var newMarkPatch = markAdvice.getMarkResult(
									rs_new_mark.mark_mode,
									rs_new_mark.mark_result,
									rs_new_mark.mark_note,
									rs_new_mark.mark_resit,
									rs_new_mark.mark_renote,
									rs_new_mark.mark_resit_limit
							);
			
						var descriptionPatch = "使用 " & rs_new_mark_patch.academic_year & "-" & rs_new_mark_patch.academic_term & "学期《" & rs_new_mark_patch.course_name & "》, 考核成绩 " & newMarkPatch & " 替换此项不及格课程";
						
						
						/* 集中替换所有不及格课程 */
						sql = "INSERT INTO t_mark_replace a 
								( 
									scm_id, 
									rep_course, 
									rep_mark, 
									rep_desc, 
									tch_id, 
									date_created 
								) 
								SELECT 
									scm_id, 
									:courseName, 
									:mark, 
									:description, 
									:teacherId, 
									:dateCreated 
								FROM t_student_mark b 
								WHERE 
									b.stu_id = :studentId 
									AND 
									b.cid = :courseId 
									AND 
										( 
											b.mark_result < 60 AND b.mark_resit < 60 
										)
									AND 
									NOT EXISTS ( 
										SELECT 1 FROM t_mark_replace a 
										WHERE 
											a.scm_id = b.scm_id 
									) ";
						
						
						queryObj = new Query( datasource=application.dnsMaster );
						
						queryObj.addParam( name="courseName", value=newCourseNamePatch, cfsqltype="cf_sql_varchar" );
						queryObj.addParam( name="mark", value=newMarkPatch, cfsqltype="cf_sql_varchar" );
						queryObj.addParam( name="description", value=descriptionPatch, cfsqltype="cf_sql_varchar" );
						queryObj.addParam( name="teacherId", value=teacherId, cfsqltype="cf_sql_varchar" );
						queryObj.addParam( name="dateCreated", value=dateCreated, cfsqltype="cf_sql_char" );
						queryObj.addParam( name="courseId", value=courseIdOld, cfsqltype="cf_sql_char" );
						queryObj.addParam( name="studentId", value=studentIdPatch, cfsqltype="cf_sql_char" );
						
						queryObj.execute( sql=sql );
						
						
						
					}
					
						
				}
				
			}

		}

	}
	
	/**
	* @hint "删除指定学生的成绩替代记录"
	* 
	* @event "MachII Event Object"
	*/
	public void function removeMarkReplace( required MachII.framework.Event event ) output=false {
		
		var markId = event.getArg("SCMID");
		
		var sql = "";
		var queryObj = ""; 
		
		if ( len(markId) ) {
			
			/* Every SQL operation is TRANSACATION*/
			transaction {
				
				sql = "DELETE FROM t_mark_replace WHERE scm_id = :markId ";
			
				queryObj = new Query( datasource=application.dnsMaster );
				queryObj.addParam( name="markId", value=markId, cfsqltype="cf_sql_varchar" );
				
				queryObj.execute( sql=sql );
			
			}
			
		}

	}

	/**
	* @hint "应届毕业生 - 替换指定学生的课程"
	* 
	* @event "MachII Event Object"
	*/
	public void function courseReplace( required MachII.framework.Event event ) output=false {
		
		var sequenceSchemaCourseReplace = getsequenceSchemaCourseReplace();
		var sequenceStudentCourseReplace = getsequenceStudentCourseReplace();
		
		var courseId = event.getArg("CIDA");
		var courseReplaceId = event.getArg("CIDB");
		
		var studentId = event.getArg("STU");
		var grade = event.getArg("GRD");
		var subjectId = event.getArg("SBJ");
		
		var toSubject = event.getArg("toSubject");
		
		var sql = "";
		var queryObj = ""; 
		
		transaction{
			
			/* 当前日期时间字符串 */
			var currentDateTime = getdatetimeAdvice().getCurrentDatetimeString();
		
			/* 当前审阅人 */
			var teacherId = getsessionAdvice().getAutherUserID();
			
			
			/*替换学生课程*/
			sequenceStudentCourseReplace.setStudentID(studentId);
			sequenceStudentCourseReplace.setCourseSource(courseId);
			sequenceStudentCourseReplace.setCourseTarget(courseReplaceId);
			
			var stuCrp = sequenceStudentCourseReplace.getID();


			sql = "SELECT 1 FROM t_student_course_replace WHERE stu_crp = :stuCrp ";
			
			queryObj = new Query( datasource=application.dnsMaster );
			
			queryObj.addParam( name="stuCrp", value=stuCrp, cfsqltype="cf_sql_varchar" );
			
			rs_check = queryObj.execute( sql=sql ).getResult();
			
			if( rs_check.recordCount ){
				
				sql = "UPDATE t_student_course_replace 
						SET 
							tch_id = :teacherId, 
							date_created = :dateCreated 
						WHERE 
							stu_crp = :stuCrp ";
				
				queryObj = new Query( datasource=application.dnsMaster );
				
				queryObj.addParam( name="stuCrp", value=stuCrp, cfsqltype="cf_sql_varchar" );
				queryObj.addParam( name="teacherId", value=teacherId, cfsqltype="cf_sql_varchar" );
				queryObj.addParam( name="dateCreated", value=currentDateTime, cfsqltype="cf_sql_char" );
				
				queryObj.execute( sql=sql );
				
			} 
			else {
				
				sql = "INSERT INTO t_student_course_replace 
							( 
								stu_crp, stu_id, cid_a, cid_b, 
								tch_id, date_created 
							) 
							VALUES (
								:stuCrp, :studentId, :courseId, :courseReplaceId,
								:teacherId, :dateCreated 
							)";
				
				queryObj = new Query( datasource=application.dnsMaster );
				
				queryObj.addParam( name="stuCrp", value=stuCrp, cfsqltype="cf_sql_varchar" );
				queryObj.addParam( name="studentId", value=studentId, cfsqltype="cf_sql_varchar" );
				queryObj.addParam( name="courseId", value=courseId, cfsqltype="cf_sql_varchar" );
				queryObj.addParam( name="courseReplaceId", value=courseReplaceId, cfsqltype="cf_sql_varchar" );
				queryObj.addParam( name="teacherId", value=teacherId, cfsqltype="cf_sql_varchar" );
				queryObj.addParam( name="dateCreated", value=currentDateTime, cfsqltype="cf_sql_char" );
				
				queryObj.execute( sql=sql );
			}
			
			/*替换专业课程*/
			if( len(toSubject) and toSubject eq "1" ) {
				
				sequenceSchemaCourseReplace.setSubjectID(subjectId);
				sequenceSchemaCourseReplace.setGrade(grade);
				sequenceSchemaCourseReplace.setCourseSource(courseId);
				sequenceSchemaCourseReplace.setCourseTarget(courseReplaceId);
				
				var shmCrp = sequenceSchemaCourseReplace.getID();
				
				sql = "SELECT 1 FROM t_schema_course_replace WHERE shm_crp = :shm_crp ";
			
				queryObj = new Query( datasource=application.dnsMaster );
				
				queryObj.addParam( name="shm_crp", value=shmCrp, cfsqltype="cf_sql_varchar" );
				
				rs_check = queryObj.execute( sql=sql ).getResult();
				
				
				if( rs_check.recordCount ){
					
					sql = "UPDATE t_schema_course_replace 
							SET 
								tch_id = :teacherId, 
								date_created = :dateCreated 
							WHERE 
								shm_crp = :shmCrp ";
					
					queryObj = new Query( datasource=application.dnsMaster );
									
					queryObj.addParam( name="shmCrp", value=shmCrp, cfsqltype="cf_sql_varchar" );
					queryObj.addParam( name="teacherId", value=teacherId, cfsqltype="cf_sql_varchar" );
					queryObj.addParam( name="dateCreated", value=currentDateTime, cfsqltype="cf_sql_char" );
					
					queryObj.execute( sql=sql );
					
				} 
				else {
					
					sql = "INSERT INTO t_schema_course_replace(
										shm_crp, sbj_id, grade, cid_a, cid_b,
										tch_id, date_created 
									)VALUES(
										:shmCrp, :subjectId, :grade, :courseId, :courseReplaceId, 
										:teacherId, :dateCreated 
									)";
					
					queryObj = new Query( datasource=application.dnsMaster );
									
					queryObj.addParam( name="shmCrp", value=shmCrp, cfsqltype="cf_sql_varchar" );
					queryObj.addParam( name="subjectId", value=subjectId, cfsqltype="cf_sql_varchar" );
					queryObj.addParam( name="grade", value=grade, cfsqltype="cf_sql_char" );
					queryObj.addParam( name="courseId", value=courseId, cfsqltype="cf_sql_varchar" );
					queryObj.addParam( name="courseReplaceId", value=courseReplaceId, cfsqltype="cf_sql_varchar" );
					queryObj.addParam( name="teacherId", value=teacherId, cfsqltype="cf_sql_varchar" );
					queryObj.addParam( name="dateCreated", value=currentDateTime, cfsqltype="cf_sql_char" );
					
					queryObj.execute( sql=sql );
					
				}
				
			}			
				
		}
		
	}
	
	/**
	* @hint "删除指定学生的课程替代记录"
	* 
	* @event "MachII Event Object"
	*/
	public void function removeCourseReplace( required MachII.framework.Event event ) output=false {
		
		var studentId = event.getArg("STU");
		var replaceId = event.getArg("REPID");
		
		var sql = "";
		var queryObj = ""; 
		
		if ( len(replaceId) ) {
			
			/* Every SQL operation is TRANSACATION*/
			transaction {

				sql = "DELETE FROM t_student_course_replace WHERE stu_crp = :replaceId ";
			
				queryObj = new Query( datasource=application.dnsMaster );
				queryObj.addParam( name="replaceId", value=replaceId, cfsqltype="cf_sql_varchar" );
				
				queryObj.execute( sql=sql );
			
			}
		}

	}

}
	
</cfscript>